shell 备份 mysql

  1. 创建备份目录和文件
1
2
3
mkdir/srv/bakmysql
mkdir/srv/bakmysql/daily
touch/srv/bakmysql/mysqlbak.log
  1. 启用二进制日志
1
2
cd /etc/
vi my.cnf

        在[mysqld]添加

1
log-bin=/var/lib/mysql/mysql-bin.log

        然后重启mysqld服务器

  1. 设置crontab任务，每天执行备份脚本
1
2
cd /etc/
vi crontab

        添加如下脚本

1
2
0 0 * * 0 root/usr/sbin/mysqlfullbak     #（每天0点执行完全备份脚本）
0 1 * * * root/usr/sbin/mysqldailybak    # (每天凌晨1点执行增量备份脚本)

        脚本内容：

  1. 完全备份脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#!/bin/bash
# Name:mysqlfullbak
# This is mysql mysqlfullbak scripts
# By Cloudsoar
# Last Modify:2012-05-10
#定义脚本存放路径
scriptsDir=/usr/sbin
#定义用户名及密码
user=root
userPWD=9dspod8a
#定义要备份的数据库
database=GoToMyCloudDB
#定义完全备份文件存放路径
bakDir=/srv/bakmysql
eMailFile=$bakDir/email.txt
eMail=xia.yan@cloudsoar.com
#定义日志文件
LogFile=$bakDir/mysqlbak.log
DATE=`date +%Y%m%d`
echo " " >> $LogFile
echo " " >> $LogFile
echo "--------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >>$LogFile
echo "-----------------" >> $LogFile
cd $bakDir
DumpFile=$DATE.sql
mysqldump --flush-logs -u$user -p$userPWD --quick $database >$DumpFile
echo "Dump Done" >> $LogFile
echo  "[$DumpFile]Backup Success!" >> $LogFile
daily_databakDir=$bakDir/daily
cd $bakDir/daily
find $daily_databakDir -name "daily*" -type f -mtime +35 -exec rm {} \; > /dev/null 2>&1
  1. 增量备份脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#!/bin/bash
# Name:mysqldailybak
# This ia mysql mysqldailybak scripts
# By Cloudsoar
# Last modify:2012-05-10
#定义用户名及密码
user=root
userPWD=9dspod8a
#定义数据库
database=GotoMyCloudDB
/usr/bin/mysqladmin -u$user -p$userPWD flush-logs
daily_databakDir=/srv/bakmysql/daily
#定义MYSQL数据目录
mysqlDataDir=/srv/mysql
eMailFile=$daily_databakDir/email.txt
eMail=xia.yan@cloudsoar.com
DATE=`date +%Y%m%d`
logFile=$daily_databakDir/mysql$DATE.log
echo "       " > $eMailFile
echo "-----------------------" >> $eMailFile 
echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile
echo "-------
------------------" >> $eMailFile
TIME=$(date "-d 10 day ago" +%Y%m%d%H%M%S)
StartTime=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S")
echo “Delete 10 days before the log” >>$eMailFile
mysql -u$user -p$userPWD -e "purge master logs before ${TIME}" && echo "delete 10 days before log" |tee -a $eMailFile #删除10天前的2进制文件
filename=`cat $mysqlDataDir/mysqld-bin.index |awk -F "/" '{print $2}'` # 2进制文件
for i in $filename
do
echo "$StartTime start backup binlog" >> $eMailFile
mysqlbinlog -u$user -p$userPWD -d $database --start-datetime="$StartTime" $mysqlDataDir/$i >> $daily_databakDir/daily$DATE.sql |tee -a $eMailFile
done
if [ $? = 0 ]
then
# 删除mtime>32的增量日志备份文件
find $daily_databakDir -name "*.log" -type f -mtime +32 -exec rm {} \; > /dev/null 2>&1
cd $daily_databakDir
echo "Daily backup succeed" >> $eMailFile
else
echo "Daily backup fail" >> $eMailFile
mail -s "MySQL Backup" $eMail < $eMailFile #备份失败之后发送邮件通知
fi
cat $eMailFile > $logFile
# 删除mtime>32的增量日志备份文件
find $daily_databakDir -name "*.log" -type f -mtime +32 -exec rm {} \; > /dev/null 2>&
  1. 删除完全备份脚本
1
2
3
4
5
6
7
8
9
#!/bin/sh
# Name:rmBackup
# PS:Delete old Backup.
# By:Cloudsoar
# Last Modify:2012-05-10
# 定义备份目录
dataBackupDir=/srv/bakmysql
# 删除mtime>32的日志备份文件
find $dataBackupDir -name "*.sql" -type f -mtime +32 -exec rm {} \; > /dev/null 2>&1
  1. 添加可执行权限

        使用

1
2
3
chmod +x mysqlfullbak
chmod +x mysqldailybak
chmod +x rmBackup